# -*- coding:UTF-8 -*-
"""
@Project:   DataCrawler
@FileName:  mysql_db.py 
@CreateDate:2023/4/27 17:32  
@Author:    Jia  
@Desc:      操作mysql数据库
"""
import pymysql
import traceback
import subprocess
from Config.log import Logs

logger = Logs().debug_logger()


class Mysql:
    def __init__(self, db_name):
        """启动docker容器，连接mysql数据库"""

        cmd = 'docker start mysql'
        res = subprocess.run(cmd, shell=True, capture_output=True, encoding='utf-8')

        if res.returncode == 0:
            logger.info('mysql容器已启动')
            self.db_name = db_name
            self.create_database()
            self.db, self.cursor = self.conn_mysql()
        else:
            logger.info('mysql容器未启动')

    def create_database(self):
        """
        检查是否存在数据库名，没有时创建
        :return:
        """
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', database='mysql')
        cursor = conn.cursor()
        cursor.execute("SHOW DATABASES")
        db_list = []
        # 遍历数据库名
        for cur in cursor:
            db_list.append(cur[0])
        if self.db_name in db_list:
            return
        else:
            cursor.execute("CREATE DATABASE " + self.db_name)

    def conn_mysql(self):
        """连接指定的指定的数据库"""

        db = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456',
                             charset='utf8', database=self.db_name)
        cursor = db.cursor()
        cursor.execute('SELECT VERSION()')
        version = cursor.fetchone()
        logger.info(f'mysql version is {version[0]}')

        if version:
            logger.info('mysql数据库连接成功')
            return db, cursor
        else:
            logger.info('mysql数据库连接失败')

    def create_table(self):
        """创建表"""
        sql = 'drop table if exists`dept_emp`'

        sql2 = 'CREATE TABLE`dept_emp`(`emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL,'\
               '`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));'

        sql3 = "drop table if exists`salaries`"

        sql4 = 'CREATE TABLE `salaries`(`emp_no` int(11) NOT NULL,`salary` int(11) NOT NULL,' \
               '`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY(`emp_no`,`from_date`));'
        try:
            self.cursor.execute(sql)
            self.cursor.execute(sql2)
            self.cursor.execute(sql3)
            self.cursor.execute(sql4)
            self.db.commit()
        except Exception as e:
            logger.info('创建表发生错误：')
            logger.error(traceback.format_exc())
            self.db.rollback()
            raise e
        else:
            logger.info('mysql表创建成功')

    def insert_data(self):
        """插入数据"""
        sql = """INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
              INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
              INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
              INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
              INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
              INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
              INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
              INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
              INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');"""

        sql2 = """INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
               INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
               INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
               INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
               INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
               INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
               INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
               INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');
               INSERT INTO salaries VALUES(10010,94409,'2001-11-23','9999-01-01');"""
        try:
            # 插入表1
            # sql_list = sql.split(';')
            # # sql_list[0:-1]列表末位元素是"需要排除
            # for i in sql_list[0:-1]:
            #     # 去掉sql语句中的空白
            #     self.cursor.execute(i.strip())
            #     self.db.commit()
            # logger.info('dept_emp表插入成功')

            # 插入表2
            sql2_list = sql2.split(';')
            for j in sql2_list[0:-1]:
                self.cursor.execute(j.strip())
                self.db.commit()
        except Exception as e:
            self.db.rollback()
            logger.error(e)
            logger.error(traceback.format_exc())


if __name__ == '__main__':
    s = Mysql('python_test')
    # s.create_table()
    s.insert_data()
    s.cursor.close()
    s.db.close()
